"""
Case Type   : objects
Case Name   : 创建with hold游标,在事务结束后fetch数据
Create At   : 2023/8/2
Owner       : @li-xin12345
Description :
    1、建表
    2、插入数据
    3、开启fetch forward from事物，创建一个游标，通过游标检索数据，结束事务后再通过游标检索数据
    4、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class TestObject(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.schema_name = 's_object_0160'
        self.table_name = f'{self.schema_name}.t_object_0160'

    def test_object(self):
        text = '-----step1:建表;expect:成功-----'
        self.log.info(text)
        sql = f'''drop schema if exists {self.schema_name} cascade;
        create schema {self.schema_name};
        drop table if exists {self.table_name} cascade;
        create table {self.table_name}
        (
            ca_address_sk             integer               not null,
            ca_address_id             char(16)              not null,
            ca_street_number          char(10)                      ,
            ca_street_name            varchar(60)                   ,
            ca_street_type            char(15)                      ,
            ca_suite_number           char(10)                      ,
            ca_city                   varchar(60)                   ,
            ca_county                 varchar(30)                   ,
            ca_state                  char(2)                       ,
            ca_zip                    char(10)                      ,
            ca_country                varchar(20)                   ,
            ca_gmt_offset             decimal(5,2)                  ,
            ca_location_type          char(20)                     
        ) with (storage_type=ustore);
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res,
                      '执行失败：' + text)

        text = '-----step2:插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''insert into {self.table_name} values(1,'a1','b1','c1','d1',
        'e1','f1','g1','h1','i1','j1',1.3,'k1');
        insert into {self.table_name} values(2,'a2','b2','c2','d2','e2','f2',
        'g2','h2','i2','j2',2.3,'k2');
        insert into {self.table_name} values(3,'a3','b3','c3','d3','e3','f3',
        'g3','h3','i3','j3',3.3,'k3');
        insert into {self.table_name} values(4,'a4','b4','c4','d4','e4','f4',
        'g4','h4','i4','j4',4.4,'k4');
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn('INSERT 0 1', res, '执行失败：' + text)
        self.assertEqual(4, res.count('INSERT 0 1'), '执行失败：' + text)

        text = '-----step3:开启fetch forward from事物，创建一个游标，' \
               '通过游标检索数据，结束事务后再通过游标检索数据;expect:成功-----'
        self.log.info(text)
        sql = f'''start transaction;
        declare cursor1 cursor with hold for select * from {self.table_name} 
        order by 1;
        fetch forward 2 from cursor1;
        end;
        fetch forward 1 from cursor1;
        close cursor1;
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.DECLEAR_CURSOR_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn('1 row', res, '执行失败：' + text)
        self.assertIn('2 rows', res, '执行失败：' + text)
        self.assertIn(self.constant.CLOSE_CURSOR_SUCCESS_MSG, res,
                      '执行失败：' + text)

    def tearDown(self):
        text = '-----step4:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f'drop schema if exists {self.schema_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')